﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class RolesDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoRoles model)
        {
            model.ROL_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Roles]");
            sql.Append("([ROL_ID],[ROL_NAME],[ROL_TYPE])");
            sql.Append("VALUES");
            sql.Append("(@ROL_ID,@ROL_NAME,@ROL_TYPE)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoRoles model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Roles] SET ");
            sql.Append("ROL_NAME=@ROL_NAME");
            sql.Append(",ROL_TYPE=@ROL_TYPE");
            sql.Append(" WHERE ");
            sql.Append("ROL_ID=@ROL_ID");

            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string rOL_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Roles] SET");
            sql.Append(" ROL_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append("ROL_ID=@ROL_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@ROL_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = rOL_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }

        /// <summary>
        /// 获取一条记录
        /// </summary>
        /// <returns>如果存在一条数据，则获取给你相应的行数，否则返回0</returns>
        public BoRoles GetModel(string rOL_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT ROL_ID,ROL_NAME,ROL_TYPE FROM Roles");
            sql.Append(" WHERE ");
            sql.Append("ROL_ID=@ROL_ID AND ROL_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@ROL_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = rOL_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoRoles> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }

        public IList<BoRoles> GetAllRoles()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT ROL_ID,ROL_NAME,ROL_TYPE FROM Roles");
            sql.Append(" WHERE ROL_IsShow='true'");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoRoles> list = GetList(table);
            return list;
        }

        public IList<BoRoles> GetRolesByeTeacher(string teacherId)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT USE_TEACHERS,USE_ROLES, ROL_ID,ROL_NAME,ROL_TYPE ");
            sql.Append(" FROM UserRoles AS U INNER JOIN Roles AS R ON U.USE_ROLES=R.ROL_ID ");
            sql.Append(" WHERE U.USE_TEACHERS=@USE_TEACHERS AND ROL_IsShow='true'");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@USE_TEACHERS", SqlDbType.NVarChar, 50);
            parameter[0].Value = teacherId;

            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), parameter);
            IList<BoRoles> list = GetList(table);
            return list;
        }

          /// <summary>
        /// 根据角色名称判断是否已经存在角色名称
        /// </summary>
        /// <param name="RoleName">角色名称</param>
        /// <returns>返回查询到的结果</returns>
        public int CheckRoleName(string RoleName)
        {
            string sql = "select count(*) from Roles where ROL_IsShow='true' and ROL_NAME=@ROL_NAME";
            SqlParameter[] parameter = new SqlParameter[1]
            {
                new SqlParameter("@ROL_NAME",RoleName),
            };
            return (int)dbhelper.ExcuteQuery(sql, parameter).Rows[0][0];
        }

        private IList<BoRoles> GetList(DataTable table)
        {
            List<BoRoles> list = new List<BoRoles>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoRoles model = new BoRoles();
                    model.ROL_ID = table.Rows[i]["ROL_ID"]==System .DBNull .Value ?"":(string)table.Rows[i]["ROL_ID"];
                    model.ROL_NAME =table.Rows[i]["ROL_NAME"]==System .DBNull.Value?"": (string)table.Rows[i]["ROL_NAME"];
                    model.ROL_TYPE = table.Rows[i]["ROL_TYPE"]==System .DBNull.Value ?0:(Int32)table.Rows[i]["ROL_TYPE"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoRoles model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@ROL_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@ROL_NAME",SqlDbType.NVarChar,50),
                new SqlParameter("@ROL_TYPE",SqlDbType.Int,4),
            };
            parameters[0].Value = model.ROL_ID;
            parameters[1].Value = model.ROL_NAME;
            parameters[2].Value = model.ROL_TYPE;
            return parameters;
        }
    }
}